ALTER procedure [dbo].[SPForumsGetByCategory]
	@UserId int = 0
AS
	declare @UserGroupId smallint
	select @UserGroupId=UserGroupId from dbo.Users where UserId=@UserId
	
	if @UserId = 0 
		SELECT
			F.ForumId
			,F.ForumName
			,F.ForumShortName
			,F.ForumDescription
			,F.UserId
			,F.ForumCreationDate
			,F.ForumTopicCount
			,F.ForumMessageCount
			,C.CategoryId
			,C.CategoryName
			,null as UnreadMessagesCount
		FROM
			ForumsCategories C
			INNER JOIN Forums F ON F.CategoryId = C.CategoryId
		WHERE
			F.Active = 1
			AND
			ISNULL(F.ReadAccessGroupId,-1) <= ISNULL(@UserGroupId,-1)
		ORDER BY
			C.CategoryOrder,
			F.ForumOrder

	else begin

		with UnreadMessages(ForumId, UnreadMessagesCount) as
		(
			select t.ForumId, count(*)
			from
				dbo.Topics t 
				left join dbo.Messages m
					on t.TopicId = m.TopicId and t.Active = 1 and m.Active = 1
				left join dbo.ReadMessages rm
					on m.TopicId = rm.TopicId and m.MessageId = rm.MessageId and rm.UserId = @UserId
			where
				m.MessageId is not null and rm.MessageId is null
			group by	t.ForumId
		)
		SELECT
			F.ForumId
			,F.ForumName
			,F.ForumShortName
			,F.ForumDescription
			,F.UserId
			,F.ForumCreationDate
			,F.ForumTopicCount
			,F.ForumMessageCount
			,C.CategoryId
			,C.CategoryName
			,isnull(um.UnreadMessagesCount, 0) as UnreadMessagesCount
		FROM
			ForumsCategories C
			INNER JOIN Forums F 
				ON F.CategoryId = C.CategoryId
			left join UnreadMessages as um
				on f.ForumId = um.ForumId
		WHERE
			F.Active = 1
			AND ISNULL(F.ReadAccessGroupId,-1) <= ISNULL(@UserGroupId,-1)
		ORDER BY
			C.CategoryOrder,
			F.ForumOrder
	end
GO